package com.vxdata.generator.数据库导出文档;

import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.db.ds.DSFactory;
import cn.hutool.setting.Setting;
import com.alibaba.fastjson2.JSONObject;
import com.vxdata.generator.数据库导出文档.vo.FieldVo;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
// 在线md转word https://products.groupdocs.app/zh/conversion/md-to-docx
public class MysqlDemo {

    public static void main(String[] args) {
        // 创建数据源
        Setting setting = new Setting();
        setting.set("url", "jdbc:mysql://localhost:3306/vxdata_nmsc?useUnicode=true&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8");
        setting.set("user", "root");
        setting.set("pass", "root");
        DataSource ds = DSFactory.create(setting).getDataSource();


        // 获取所有表
        String databaseName = "vxdata_nmsc";
        List<JSONObject> tableList = getTableStr(ds, databaseName);

        // 获取表下字段信息
        Map<String, List<FieldVo>> fieldVoMap = new HashMap<>();
        tableList.forEach(t -> {
            String tableName = t.getString("tablename");
            List<FieldVo> fieldList = getFieldStr(ds, databaseName, tableName);
            fieldVoMap.put(tableName, fieldList);
        });

        String s = mdStr(tableList, fieldVoMap);
        System.out.println(s);
    }

    /**
     * 组装md语句
     *
     * @return
     */
    public static String mdStr(List<JSONObject> tableList, Map<String, List<FieldVo>> fieldVoMap) {
        System.out.println("\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n");
        StringBuilder mdStr = new StringBuilder();
        mdStr.append("## 1. 表总览\n");
        mdStr.append("| **表名** | **备注** | **字段数量** |\n");
        mdStr.append("| :-------- | :-------- | :-------- |\n");
        tableList.forEach(t -> {
            mdStr.append("|" + t.getString("tablename") + "|" + t.getString("tablecomment") + "|" + t.getString("columncount") + "|\n");
        });
        mdStr.append("\n\n");

        mdStr.append("## 2. 表详情\n");
        Integer i = 1;
        for (JSONObject t : tableList) {
            String tableName = t.getString("tablename");
            mdStr.append("#### 2." + i + " " + tableName + t.getString("tablecomment") + "\n");

            mdStr.append("| **字段名称** | **字段类型(大小)** | **允许为空 (YES/NO)** | **字段说明** |\n");
            mdStr.append("| :-------- | :-------------- | :----------------- | :-------- |\n");
            List<FieldVo> fieldVoList = fieldVoMap.get(tableName);
            fieldVoList.forEach(f -> {
                mdStr.append("|" + f.getFieldname() + "|" + f.getFieldtypewithlength() + "|" + f.getIsnullable() + "|" + f.getFieldcomment() + "|\n");
            });
            mdStr.append("\n\n\n");
            i++;
        }

        return mdStr.toString();
    }


    /**
     * 获取数据库中所有表信息
     *
     * @param ds           数据源配置
     * @param databaseName 数据源名称
     * @return
     */
    public static List<JSONObject> getTableStr(DataSource ds, String databaseName) {
        String sqlStr = "SELECT t.TABLE_NAME AS tableName, " +
                "t.TABLE_COMMENT AS tableComment," +
                "COUNT(c.COLUMN_NAME) AS columnCount " +
                "FROM information_schema.TABLES t " +
                "LEFT JOIN information_schema.COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME AND t.TABLE_SCHEMA = c.TABLE_SCHEMA" +
                " WHERE t.TABLE_SCHEMA = '" + databaseName + "' GROUP BY t.TABLE_NAME, t.TABLE_COMMENT;";
        List<JSONObject> resultList = getResultList(ds, sqlStr);
        return resultList;
    }

    /**
     * 获取表字段信息
     *
     * @param ds           数据源配置
     * @param databaseName 数据源名称
     * @param tableName    表名称
     * @return
     */
    public static List<FieldVo> getFieldStr(DataSource ds, String databaseName, String tableName) {
        String sqlStr = "SELECT \n" +
                "    COLUMN_NAME AS fieldName, \n" +
                "    CASE \n" +
                "        WHEN DATA_TYPE IN ('varchar', 'char') THEN CONCAT(DATA_TYPE, '(', CHARACTER_MAXIMUM_LENGTH, ')')\n" +
                "        WHEN DATA_TYPE IN ('decimal', 'numeric') THEN CONCAT(DATA_TYPE, '(', NUMERIC_PRECISION, ',', NUMERIC_SCALE, ')')\n" +
                "        WHEN DATA_TYPE IN ('int', 'tinyint', 'smallint', 'mediumint', 'bigint') THEN CONCAT(DATA_TYPE, '(', NUMERIC_PRECISION, ')')\n" +
                "        WHEN DATA_TYPE IN ('float', 'double') THEN CONCAT(DATA_TYPE, '(', NUMERIC_PRECISION, ')')\n" +
                "        WHEN DATA_TYPE IN ('date', 'time', 'datetime', 'timestamp') THEN DATA_TYPE\n" +
                "        ELSE DATA_TYPE \n" +
                "    END AS fieldTypeWithLength,\n" +
                "    IS_NULLABLE AS isNullable,\n" +
                "    COLUMN_DEFAULT AS defaultValue,\n" +
                "    COLUMN_COMMENT AS fieldComment\n" +
                "FROM \n" +
                "    information_schema.COLUMNS\n" +
                "WHERE \n" +
                "    TABLE_SCHEMA = '" + databaseName + "' AND TABLE_NAME = '" + tableName + "';";
        List<JSONObject> resultList = getResultList(ds, sqlStr);

        List<FieldVo> fieldVoList = new ArrayList<>();
        for (JSONObject o : resultList) {
            FieldVo vo = o.toJavaObject(FieldVo.class);
            fieldVoList.add(vo);
        }
        return fieldVoList;
    }


    /**
     * 执行sql, 返回结果集
     *
     * @param ds     数据源配置
     * @param sqlStr sql语句
     * @return
     */
    public static List<JSONObject> getResultList(DataSource ds, String sqlStr) {
        List<JSONObject> resultList = new ArrayList<>();
        try {
            // 执行查询
            List<Entity> entityList = Db.use(ds).query(sqlStr);
            for (Entity entity : entityList) {
                resultList.add(JSONObject.parse(JSONObject.toJSONString(entity)));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultList;
    }


}
